﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using University.Workshop.Model;

namespace University.Workshop.DataAccess
{
    public static class WebmasterRequestDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static WebmasterRequest InsertNewWebmasterRequest(WebmasterRequest newRequest)
        {
            if (newRequest != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [WEBMASTERREQUEST](CandidateId, Skills, Date, Status, WantsToReplyTickets, WantsToSetWebMasters, PositiveVotes, NegativeVotes)" +
                                       " VALUES(@CandidateId, @Skills, @Date, @Status, @WantsToReplyTickets, @WantsToSetWebMasters, @PositiveVotes, @NegativeVotes)";
                    cmd.Parameters.AddWithValue("CandidateId", newRequest.CandidateId);
                    cmd.Parameters.AddWithValue("Skills", newRequest.Skills);
                    cmd.Parameters.AddWithValue("Date", newRequest.Date);
                    cmd.Parameters.AddWithValue("Status", (int)newRequest.Status);
                    cmd.Parameters.AddWithValue("WantsToReplyTickets", newRequest.WantsToReplyTickets);
                    cmd.Parameters.AddWithValue("WantsToSetWebMasters", newRequest.WantsToSetWebmasters);
                    cmd.Parameters.AddWithValue("PositiveVotes", newRequest.PositiveVotes);
                    cmd.Parameters.AddWithValue("NegativeVotes", newRequest.NegativeVotes);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a webmaster request");
                    }
                    newRequest.Id = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return newRequest;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting a webmaster request", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static WebmasterRequest GetWebmasterRequestByCandidateId(long userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [WEBMASTERREQUEST] Where CandidateId = @candidateId";
                cmd.Parameters.AddWithValue("candidateId", userId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                WebmasterRequest webmasterReq = new WebmasterRequest();

                while (reader.Read())
                {
                    webmasterReq.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.CandidateId = Int64.Parse(reader["CandidateId"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Skills = reader["Skills"].ToString();
                    webmasterReq.Date = DateTime.Parse(reader["Date"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Status = WebmasterRequestStatus.Voting + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.WantsToReplyTickets = bool.Parse(reader["WantsToReplyTickets"].ToString());
                    webmasterReq.WantsToSetWebmasters = bool.Parse(reader["WantsToSetWebMasters"].ToString());
                    webmasterReq.PositiveVotes = Int32.Parse(reader["PositiveVotes"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.NegativeVotes = Int32.Parse(reader["NegativeVotes"].ToString(), CultureInfo.CurrentCulture);
                }
                return webmasterReq;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a webmaster request", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<WebmasterRequest> GatherAllVotingRequests()
        {
            SqlConnection conn = null;
            try
            {
                Collection<WebmasterRequest> allRequests = new Collection<WebmasterRequest>();
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [WEBMASTERREQUEST] Where Status = @status";
                cmd.Parameters.AddWithValue("status", (int)WebmasterRequestStatus.Voting);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    WebmasterRequest webmasterReq = new WebmasterRequest();
                    webmasterReq.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.CandidateId = Int64.Parse(reader["CandidateId"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Skills = reader["Skills"].ToString();
                    webmasterReq.Date = DateTime.Parse(reader["Date"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Status = WebmasterRequestStatus.Voting + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.WantsToReplyTickets = bool.Parse(reader["WantsToReplyTickets"].ToString());
                    webmasterReq.WantsToSetWebmasters = bool.Parse(reader["WantsToSetWebMasters"].ToString());
                    webmasterReq.PositiveVotes = Int32.Parse(reader["PositiveVotes"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.NegativeVotes = Int32.Parse(reader["NegativeVotes"].ToString(), CultureInfo.CurrentCulture);
                    allRequests.Add(webmasterReq);
                }
                return allRequests;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a webmaster request", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static WebmasterRequest GetWebmasterRequestById(long requestId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [WEBMASTERREQUEST] Where Id = @id";
                cmd.Parameters.AddWithValue("id", requestId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                WebmasterRequest webmasterReq = new WebmasterRequest();

                while (reader.Read())
                {
                    webmasterReq.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.CandidateId = Int64.Parse(reader["CandidateId"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Skills = reader["Skills"].ToString();
                    webmasterReq.Date = DateTime.Parse(reader["Date"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.Status = WebmasterRequestStatus.Voting + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.WantsToReplyTickets = bool.Parse(reader["WantsToReplyTickets"].ToString());
                    webmasterReq.WantsToSetWebmasters = bool.Parse(reader["WantsToSetWebMasters"].ToString());
                    webmasterReq.PositiveVotes = Int32.Parse(reader["PositiveVotes"].ToString(), CultureInfo.CurrentCulture);
                    webmasterReq.NegativeVotes = Int32.Parse(reader["NegativeVotes"].ToString(), CultureInfo.CurrentCulture);
                }
                return webmasterReq;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a webmaster request", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static WebmasterRequest UpdateRequestVotes(long requestId, int extraVotes)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                WebmasterRequest webmasterReq = GetWebmasterRequestById(requestId);
                if (extraVotes > 0)
                {
                    webmasterReq.PositiveVotes++;
                }
                else if (extraVotes < 0)
                {
                    webmasterReq.NegativeVotes++;
                }
                cmd.CommandText =
                    "UPDATE [WEBMASTERREQUEST] SET PositiveVotes=@positiveVotes, NegativeVotes=@negativeVotes WHERE Id=@requestId";
                cmd.Parameters.AddWithValue("requestId", requestId);
                cmd.Parameters.AddWithValue("positiveVotes", webmasterReq.PositiveVotes);
                cmd.Parameters.AddWithValue("negativeVotes", webmasterReq.NegativeVotes);
                connection.Open();
                int resp = cmd.ExecuteNonQuery();
                if (resp <= 0)
                {
                    return null;
                }
                return webmasterReq;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error updating webmaster request information", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static WebmasterRequest FinishRequest(long requestId)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText =
                    "UPDATE [WEBMASTERREQUEST] SET Status=@status WHERE Id=@requestId";
                cmd.Parameters.AddWithValue("requestId", requestId);
                cmd.Parameters.AddWithValue("status", (int)WebmasterRequestStatus.Finished);
                connection.Open();
                int resp = cmd.ExecuteNonQuery();
                if (resp <= 0)
                {
                    return null;
                }
                return GetWebmasterRequestById(requestId);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error updating webmaster request information", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void RecordVote(string userEmail, long requestId)
        {
            SqlConnection conn = null;
            try
            {
                long userId;
                if(userEmail!=null)
                    userId = UserDataAccess.GetUserByEmail(userEmail).Id;
                else
                {
                    throw new DataAccessException("The votator user is not valid!");
                }
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO [REQUESTSVOTES](VotatorId, RequestId)" +
                                    " VALUES(@votatorId, @requestId)";
                cmd.Parameters.AddWithValue("votatorId", userId);
                cmd.Parameters.AddWithValue("requestId", requestId);
                SqlCommand cmdId = conn.CreateCommand();
                cmdId.CommandText = "SELECT @@Identity";
                conn.Open();
                int result = cmd.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error inserting a vote at request");
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error inserting a vote at request", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static bool HasVoted(string userEmail, long requestId)
        {
            SqlConnection conn = null;
            try
            {
                long userId;
                if (userEmail != null)
                    userId = UserDataAccess.GetUserByEmail(userEmail).Id;
                else
                {
                    throw new DataAccessException("The votator user is not valid!");
                }
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [REQUESTSVOTES] Where RequestId = @requestId AND VotatorId = @votatorId";
                cmd.Parameters.AddWithValue("requestId", requestId);
                cmd.Parameters.AddWithValue("votatorId", userId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return false;
                }
                return true;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a webmaster request", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
